﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using YY.Common;
using WebApp.BaseDataSvc;
using System.Text;
using System.Web.Services;
using Yogesh.ExcelXml;
using System.Data;
using System.Collections;
using System.Web.Script.Serialization;
using System.Web.SessionState;

namespace WebApp
{
    /// <summary>
    /// $codebehindclassname$ 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    public class ExportWTS : IHttpHandler, IRequiresSessionState
    {

        public void ProcessRequest(HttpContext context)
        {
            HttpResponse contextResponse = context.Response;
            contextResponse.Clear();
            contextResponse.Buffer = true;
            var dataType = context.Request.Params["DataType"];
            switch (dataType)
            {
                case "WTS":
                    string excelName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 
                    contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", excelName)); //定义输出文件和文件名
                    //contextResponse.AppendHeader("Content-Length", content.Length.ToString());
                    contextResponse.ContentEncoding = Encoding.Default;
                    contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
                    var orderId = Convert.ToInt32(context.Request.Params["OrderId"]);
                    exportWTS(orderId, contextResponse);
                    break;
                case "PCD":
                    printPCDPre(context, contextResponse, Convert.ToInt32(context.Request.Params["OrderId"]));
                    break;
                case "BGWTS":
                    PrintBGWTSPre(context, contextResponse, Convert.ToInt32(context.Request.Params["OrderId"]));
                    break;
                case "Bill":
                    var sDate = Convert.ToDateTime(context.Request.Params["SDate"]);
                    var eDate = Convert.ToDateTime(context.Request.Params["EDate"]);
                    var target = Convert.ToInt32(context.Request.Params["Target"]);
                    int? skAccount = null;
                    if (!string.IsNullOrEmpty(context.Request.Params["Account"]))
                        skAccount = Convert.ToInt32(context.Request.Params["Account"]);
                    bool export = false;
                    if (!string.IsNullOrEmpty(context.Request.Params["Export"]))
                        export = Convert.ToBoolean(context.Request.Params["Export"]);
                    var feeType = (EnumCommon.FeeType)Enum.Parse(typeof(EnumCommon.FeeType), context.Request.Params["FeeType"]);
                    string[] ckIdAry = null;
                    if (export)
                    {
                        if (!string.IsNullOrEmpty(context.Request.Params["CKIDS"]))
                            ckIdAry = context.Request.Params["CKIDS"].Split(',');
                    }

                    PrintBillPre(export, context, contextResponse, sDate, eDate, feeType, target, skAccount, ckIdAry);
                    //getBill(contextResponse, sDate, eDate, feeType, target, export);
                    break;
                default:
                    break;
            }
            contextResponse.End();
        }

        /// <summary>
        /// 输出委托书
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="contextResponse"></param>
        void exportWTS(int orderId, HttpResponse contextResponse)
        {
            ExcelXmlWorkbook book = new ExcelXmlWorkbook();
            BaseDataSvc.BaseDataClient BaseDataProxy = new BaseDataClient();
            string rtn = string.Empty;
            try
            {
                BaseDataProxy.Open();
                var trailer = BaseDataProxy.GetTrailer(orderId);
                var order = BaseDataProxy.GetOrder(orderId);

                if (trailer == null | order == null)
                    return;
                TB_Options o = BaseDataProxy.GetOption(trailer.agent);
                TB_User op = BaseDataProxy.GetUser(order.agent);
                BaseDataProxy.Close();

                string tcClientName = string.Empty;

                if (o != null)
                    tcClientName = o.name;

                string deadLineTime = string.Empty;
                deadLineTime = trailer.deadline;

                string charge = string.Empty;
                if (trailer.charge.HasValue)
                    charge = trailer.charge.Value.ToString();



                book.Properties.Author = "盈泓";
                Worksheet sheet = book[0];
                sheet.Name = "拖车委托书";

                // More on this in documentation
                //sheet.FreezeTopRows = 3;

                // and this too...
                sheet.PrintOptions.Orientation = PageOrientation.Landscape;
                sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);

                // This is the actual code which sets out the cell values
                // Note again, that we don't declare any instance at all.
                // All the work is done by the library.
                // Index operator takes first value as column and second as row.

                AlignmentOptions al = new AlignmentOptions();
                al.Horizontal = HorizontalAlignment.Center;
                al.Vertical = VerticalAlignment.Center;

                AlignmentOptions al1 = new AlignmentOptions();
                al1.Horizontal = HorizontalAlignment.Left;
                al1.Vertical = VerticalAlignment.Center;

                XmlStyle style1 = new XmlStyle();
                style1.Font.Bold = true;
                style1.DisplayFormat = DisplayFormatType.Text;
                style1.Font.Size = 30;
                style1.Font.Name = "宋体";
                style1.Alignment = al;


                XmlStyle style2 = new XmlStyle();
                style2.Font.Bold = true;
                style2.DisplayFormat = DisplayFormatType.Text;
                style2.Font.Size = 20;
                style2.Font.Name = "宋体";
                style2.Alignment = al;

                XmlStyle style3 = new XmlStyle();
                style3.Font.Bold = true;
                style3.DisplayFormat = DisplayFormatType.Text;
                style3.Font.Size = 15;
                style3.Font.Name = "宋体";
                style3.Alignment = al1;

                sheet[0, 0].Value = "广州市盈洋货运代理有限公司";
                sheet[0, 0].Style = style1;

                sheet[0, 1].Value = "";
                sheet[0, 1].Style = style3;

                sheet[0, 2].Value = "派车委托书";
                sheet[0, 2].Style = style2;

                sheet[0, 3].Value = "致" + tcClientName + " " + trailer.stowageContact;
                sheet[0, 3].Style = style3;

                sheet[0, 4].Value = "兹委托贵公司派车到以下地点装货，资料如下：";
                sheet[0, 4].Style = style3;

                sheet[0, 5].Value = "1、订舱号：" + order.cCode;
                sheet[0, 5].Style = style3;

                sheet[0, 6].Value = "到厂装货时间：" + deadLineTime;
                sheet[0, 6].Style = style3;

                sheet[0, 7].Value = "2、装货地点：" + trailer.address;
                sheet[0, 7].Style = style3;

                sheet[0, 8].Value = "3、联系人：" + trailer.stowageContact;
                sheet[0, 8].Style = style3;

                sheet[0, 9].Value = "4、柜型：" + trailer.specs;
                sheet[0, 9].Style = style3;

                sheet[0, 10].Value = "5、运费：" + charge;
                sheet[0, 10].Style = style3;

                sheet[0, 11].Value = "6、备注：" + trailer.memo;
                sheet[0, 11].Style = style3;

                sheet[0, 12].Value = "7、（请准时到达。柜号司机资料麻烦到时发到15018476677 谢谢！）";
                sheet[0, 12].Style = style3;

                sheet[0, 13].Value = "";
                sheet[0, 13].Style = style3;

                sheet[0, 14].Value = "操作人员：" + op.userName;
                sheet[0, 14].Style = style3;

                sheet[0, 15].Value = "我司单号：" + order.code;
                sheet[0, 15].Style = style3;

                sheet[0, 16].Value = "座机号：020-28945185";
                sheet[0, 16].Style = style3;

                sheet[0, 17].Value = "手机：" + op.tel;
                sheet[0, 17].Style = style3;

                sheet[0, 18].Value = "传真号：020-32372253";
                sheet[0, 18].Style = style3;

                sheet[0, 19].Value = "日期：" + DateTime.Now.ToString("yyyy-MM-dd");
                sheet[0, 19].Style = style3;

                //"2、装货地点：" + trailer.address
                sheet.Columns(0).Width = 543;


                // no extension is added if not present
                //string outputFile = "Outstanding File.xml";

                book.Export(contextResponse.OutputStream);
                //book.Export(outputFile);




            }
            catch (Exception ex)
            {
                throw ex;
            }
            //return rtn;
        }

        /// <summary>
        /// 输出派车单预处理
        /// </summary>
        /// <param name="orderId"></param>
        void printPCDPre(HttpContext context, HttpResponse contextResponse, int orderId)
        {
            try
            {
                BaseDataSvc.BaseDataClient BaseDataProxy = new BaseDataClient();
                BaseDataProxy.Open();
                TB_Order order = BaseDataProxy.GetOrder(orderId);
                if (order == null)
                    return;
                TB_Trailer trailer = BaseDataProxy.GetTrailer(orderId);
                if (trailer == null)
                    return;
                IEnumerable<TB_Options> options = BaseDataProxy.FindOptions(null, null, null);
                var provider = options.SingleOrDefault(p => p.id == trailer.agent);
                if (provider == null)
                    return;
                TB_User op = BaseDataProxy.GetUser(order.agent);
                if (op == null)
                    return;
                var feeItem = options.SingleOrDefault(p => p.name == "拖车费" & p.type == EnumCommon.OptionType.费目类型.ToString());
                if (feeItem == null)
                {
                    //MessageBox.Show("没找到[拖车费]费目数据！");
                    return;
                }

                decimal fee = 0;//应付总费用
                int recordCount = 0;
                int pageCount = 0;

                TB_User user = context.Session["User"] as TB_User;
                if (user == null)
                    return;

                var fees = BaseDataProxy.FindFees2(out recordCount, out pageCount, order.code, null, null, null, null, null, (int)EnumCommon.FeeType.应付, null, feeItem.id, null, null, user.id, user.role, 10000, 1);
                if (fees != null)
                {
                    foreach (var f in fees)
                        fee += f.Amount;

                }

                var deadline = string.Empty;
                //if (trailer.deadline.HasValue)
                //deadline = trailer.deadline.Value.ToString("yyyy-MM-dd hh:mm");
                deadline = trailer.deadline;
                var zgAddress = trailer.address;
                var zgContact = trailer.stowageContact;
                var returnPort = string.Empty;
                if (trailer.returnPort.HasValue)
                    returnPort = options.SingleOrDefault(p => p.id == trailer.returnPort.Value).name;
                var tcMemo = trailer.memo;
                PrintPCD(contextResponse, "广州市盈泓货运代理有限公司", provider.name, op.userName, op.tel, op.mobile, order.code, order.cCode, trailer.specs, fee.ToString("N2"), tcMemo, deadline, zgAddress, zgContact, returnPort);

                BaseDataProxy.Close();
            }
            catch { }
        }

        void PrintPCD(HttpResponse contextResponse, string comTitle, string providerName, string opName, string opTel, string opMobile, string orderCode, string dcCode,
            string gx, string tcFee, string tcMemo, string deadline, string zgAddress, string zgContact, string returnPort)
        {

            try
            {
                ExcelXmlWorkbook book = new ExcelXmlWorkbook();
                contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 
                contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", "派车单.xls")); //定义输出文件和文件名
                //contextResponse.AppendHeader("Content-Length", content.Length.ToString());
                contextResponse.ContentEncoding = Encoding.Default;
                contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 

                book.Properties.Author = "盈泓";
                Worksheet sheet = book[0];
                sheet.Name = "派车单";

                // and this too...
                sheet.PrintOptions.Orientation = PageOrientation.Portrait;
                sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);


                XmlStyle style21 = new XmlStyle();
                style21.Alignment.Vertical = VerticalAlignment.Bottom;


                XmlStyle styles22 = new XmlStyle();
                styles22.Font.Size = 16;
                styles22.Font.Name = "宋体";

                XmlStyle styles23 = new XmlStyle();
                styles23.Font.Size = 18;
                styles23.Font.Name = "宋体";

                XmlStyle styles35 = new XmlStyle();
                styles35.Font.Size = 18;
                styles35.Font.Name = "宋体";
                styles35.Alignment.Vertical = VerticalAlignment.Bottom;
                styles35.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;

                XmlStyle styles36 = new XmlStyle();
                styles36.Font.Size = 14;
                styles36.Font.Name = "Times New Roman";
                styles36.Alignment.Vertical = VerticalAlignment.Bottom;
                styles36.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                styles36.Font.Bold = true;

                XmlStyle styles37 = new XmlStyle();
                styles37.Font.Size = 12;
                styles37.Font.Name = "宋体";
                styles37.Alignment.Vertical = VerticalAlignment.Bottom;
                styles37.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                styles37.Font.Bold = true;
                styles37.Border.LineStyle = Borderline.Double;
                styles37.Border.Weight = 3;
                styles37.Border.Sides = BorderSides.Bottom;

                XmlStyle styles38 = new XmlStyle();
                styles38.Font.Size = 18;
                styles38.Font.Name = "宋体";
                styles38.Alignment.Vertical = VerticalAlignment.Bottom;
                styles38.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                styles38.Alignment.WrapText = true;

                XmlStyle styles39 = new XmlStyle();
                styles39.Font.Size = 16;
                styles39.Font.Name = "宋体";
                styles39.Alignment.Vertical = VerticalAlignment.Bottom;
                styles39.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;

                XmlStyle styles40 = new XmlStyle();
                styles40.Alignment.Vertical = VerticalAlignment.Bottom;
                styles40.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;

                XmlStyle styles41 = new XmlStyle();
                styles41.Font.Size = 18;
                styles41.Font.Name = "宋体";
                styles41.Alignment.Vertical = VerticalAlignment.Center;
                styles41.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                styles41.Alignment.WrapText = true;

                XmlStyle styles42 = new XmlStyle();
                styles42.Font.Size = 24;
                styles42.Font.Name = "宋体";
                styles42.Font.Bold = true;
                styles42.Alignment.Vertical = VerticalAlignment.Justify;
                styles42.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                styles42.Alignment.WrapText = true;

                XmlStyle styles43 = new XmlStyle();
                styles43.Font.Size = 24;
                styles43.Font.Name = "宋体";
                styles43.Font.Bold = true;
                styles43.Alignment.Vertical = VerticalAlignment.Center;
                styles43.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                styles43.Border.LineStyle = Borderline.Double;
                styles43.Border.Weight = 3;
                styles43.Border.Sides = BorderSides.Top;

                XmlStyle styles45 = new XmlStyle();
                styles45.Font.Size = 18;
                styles45.Font.Name = "宋体";
                styles45.Alignment.Vertical = VerticalAlignment.Bottom;
                styles45.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;

                sheet[0, 0].Value = comTitle;
                sheet[0, 0].Style = styles42;

                sheet[0, 1].Value = "GUANGZHOU YINGHONG LOGISTICS CO.,LTD";
                sheet[0, 1].Style = styles36;

                sheet[0, 2].Value = "地址Add：广州市黄埔区骏雅北街3号金碧领秀国际商务中心804房";
                sheet[0, 2].Style = styles37;

                sheet[0, 3].Value = "派车单";
                sheet[0, 3].Style = styles43;

                sheet[0, 4].Value = "TO:" + providerName;
                sheet[0, 4].Style = styles38;

                sheet[0, 5].Value = "FM:盈泓/" + opName + "   " + opTel;//客服名称 电话
                sheet[0, 5].Style = styles38;

                sheet[0, 6].Value = "";
                sheet[0, 6].Style = styles35;

                sheet[0, 7].Value = "我司单号:" + orderCode;
                sheet[0, 7].Style = styles38;

                sheet[0, 8].Value = "";
                sheet[0, 8].Style = styles35;

                sheet[0, 9].Value = "订 舱 号:" + dcCode;//提单号
                sheet[0, 9].Style = styles38;

                sheet[0, 10].Value = "柜    型：" + gx;
                sheet[0, 10].Style = styles38;

                sheet[0, 11].Value = "";
                sheet[0, 11].Style = styles45;

                sheet[0, 12].Value = "装柜时间：" + deadline;
                sheet[0, 12].Style = styles38;

                sheet[0, 13].Value = "";
                sheet[0, 13].Style = styles40;

                sheet[0, 14].Value = "装柜地点：" + zgAddress + " " + zgContact;
                sheet[0, 14].Style = styles38;

                sheet[0, 15].Value = "";
                sheet[0, 15].Style = styles39;

                sheet[0, 16].Value = "";
                sheet[0, 16].Style = styles41;

                sheet[0, 17].Value = "";
                sheet[0, 17].Style = styles35;

                sheet[0, 18].Value = "还柜码头:" + returnPort;
                sheet[0, 18].Style = styles38;

                sheet[0, 19].Value = "拖车备注:" + tcMemo;
                sheet[0, 19].Style = styles38;

                sheet[0, 20].Value = "";
                sheet[0, 20].Style = styles35;

                sheet[0, 21].Value = "";
                sheet[0, 21].Style = styles35;

                sheet[0, 22].Value = "柜号请发到我司联系人/手机：" + opName + "  " + opMobile;
                sheet[0, 22].Style = styles38;

                sheet[0, 23].Value = "费用确认：RMB" + tcFee;
                sheet[0, 23].Style = styles38;

                sheet[0, 24].Value = "";
                sheet[0, 24].Style = styles35;

                sheet[0, 25].Value = "如有实报实销的费用请在拖车完成后及时回传！";
                sheet[0, 25].Style = styles23;

                sheet[0, 26].Value = "请及时安排！";
                sheet[0, 26].Style = styles35;

                sheet[0, 27].Value = "";
                sheet[0, 27].Style = styles35;

                sheet[0, 28].Value = "合作愉快！TKS！";
                sheet[0, 28].Style.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right;
                sheet[0, 28].Style.Font.Size = 18;
                sheet[0, 28].Style.Font.Name = "宋体";

                sheet.Columns(0).Width = 500;
                //sheet.Columns(0).Style.Alignment.ShrinkToFit = true; ;
                for (int i = 0; i <= 28; i++)
                    sheet[i].Style.Alignment.ShrinkToFit = true;

                // no extension is added if not present
                //string outputFile = "Outstanding File.xml";

                book.Export(contextResponse.OutputStream);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 输出报关委托书预处理
        /// </summary>
        void PrintBGWTSPre(HttpContext context, HttpResponse contextResponse, int orderId)
        {
            try
            {
                TB_User user = context.Session["User"] as TB_User;

                BaseDataSvc.BaseDataClient BaseDataProxy = new BaseDataClient();
                BaseDataProxy.Open();
                TB_Order order = BaseDataProxy.GetOrder(orderId);
                if (order == null)
                    return;
                TB_Customs customs = BaseDataProxy.GetCustoms(order.id);
                if (customs == null)
                    return;
                IEnumerable<TB_Options> options = BaseDataProxy.FindOptions(null, null, null);
                IEnumerable<TB_Case> xhList = BaseDataProxy.GetCases(order.id);
                var bgAgent = options.SingleOrDefault(p => p.id == customs.agent);
                var op = BaseDataProxy.GetUser(order.agent);
                string startPort = string.Empty;
                if (order.startPort > 0)
                    startPort = options.SingleOrDefault(p => p.id == order.startPort).name;
                var jgTime = string.Empty;
                if (order.customsDeadline.HasValue)
                    jgTime = order.customsDeadline.Value.ToString("yyyy-MM-dd");
                var trasferType = string.Empty;
                if (customs.transferType.HasValue)
                    trasferType = options.SingleOrDefault(p => p.id == customs.transferType.Value).name;
                var zgxs = string.Empty;
                if (order.stowageMethod.HasValue)
                    zgxs = options.SingleOrDefault(p => p.id == order.stowageMethod.Value).name;
                var bgMemo = customs.memo;
                List<string[]> cargos = new List<string[]>();
                if (xhList != null)
                {
                    foreach (TB_Case xh in xhList)
                    {
                        var ss = xh.code + "/" + xh.model;

                        //加载货物
                        var goods = BaseDataProxy.GetCargos(xh.id);
                        if (goods != null)
                        {
                            foreach (TB_Cargo cg in goods)
                            {
                                //IEnumerable<TB_Case> cases = xhList.Where(p => p.id == cg.@case);                                
                                //if (cases != null & cases.Any())
                                //{
                                //var c = cases.First();
                                //var ss = c.code + "/" + c.model;
                                //var ss = rs[0].Field<string>("code") + "/" + rs[0].Field<string>("model");
                                cargos.Add(new string[] { ss, xh.title, cg.amount, cg.hairWeight, cg.cargoName });
                                //}
                            }
                        }
                    }
                }

                decimal fee = 0;
                var feeItem = options.SingleOrDefault(p => p.name == "报关费" & p.type == EnumCommon.OptionType.费目类型.ToString());
                int recordCount = 0;
                int pageCount = 0;
                var fees = BaseDataProxy.FindFees2(out recordCount, out pageCount, order.code, null, null, null, null, null, (int)EnumCommon.FeeType.应付, null, feeItem.id, null, null, user.id, user.role, 10000, 1);
                if (fees != null)
                {
                    foreach (var f in fees)
                        fee += f.Amount;

                }
                PrintBGWTS(contextResponse, "广州市盈泓货运代理有限公司", bgAgent.name, op.userName, op.tel, order.code, order.cCode, startPort, jgTime, customs.address, trasferType, zgxs, cargos, fee.ToString("N2"), bgMemo);
                BaseDataProxy.Close();
            }
            catch { }
        }

        void PrintBGWTS(HttpResponse contextResponse, string comTitle, string bgAgent, string opName, string opTel, string orderCode, string cCode, string startPort, string jgTime, string fxtAddress, string transferType, string zgxs, List<string[]> cargos, string fee, string memo)
        {

            try
            {
                ExcelXmlWorkbook book = new ExcelXmlWorkbook();
                contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 
                contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", "报关委托书.xls")); //定义输出文件和文件名
                //contextResponse.AppendHeader("Content-Length", content.Length.ToString());
                contextResponse.ContentEncoding = Encoding.Default;
                contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 

                book.Properties.Author = "盈泓";
                Worksheet sheet = book[0];
                sheet.Name = "报关委托书";

                sheet.PrintOptions.Orientation = PageOrientation.Landscape;
                sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);


                XmlStyle m24864368 = new XmlStyle();
                m24864368.Alignment.Vertical = VerticalAlignment.Bottom;
                m24864368.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                m24864368.Border.LineStyle = Borderline.Continuous;
                m24864368.Border.Weight = 1;
                m24864368.Border.Sides = BorderSides.All;

                XmlStyle s21 = new XmlStyle();

                XmlStyle s22 = new XmlStyle();//s48
                s22.Alignment.Vertical = VerticalAlignment.Bottom;

                XmlStyle s25 = new XmlStyle();
                s25.Alignment.Vertical = VerticalAlignment.Bottom;
                s25.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s25.Border.LineStyle = Borderline.Continuous;
                s25.Border.Weight = 1;
                s25.Border.Sides = BorderSides.All;
                s25.Font.Name = "宋体";
                s25.Font.Size = 16;

                XmlStyle s45 = new XmlStyle();
                s45.Alignment.Vertical = VerticalAlignment.Bottom;
                s45.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;

                XmlStyle s46 = new XmlStyle();
                s46.Alignment.Vertical = VerticalAlignment.Bottom;
                s46.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                s46.Font.Name = "宋体";
                s46.Font.Size = 16;

                XmlStyle s47 = new XmlStyle();
                s47.Alignment.Vertical = VerticalAlignment.Bottom;
                s47.Font.Name = "宋体";
                s47.Font.Size = 16;

                XmlStyle s51 = new XmlStyle();
                s51.Alignment.Vertical = VerticalAlignment.Bottom;
                s51.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s51.Font.Name = "楷体_GB2312";
                s51.Font.Size = 20;
                s51.Font.Bold = true;

                XmlStyle s52 = new XmlStyle();
                s52.Alignment.Vertical = VerticalAlignment.Bottom;
                s52.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s52.Border.Sides = BorderSides.Bottom;
                s52.Border.LineStyle = Borderline.Double;
                s52.Border.Weight = 3;
                s52.Font.Name = "Arial";
                s52.Font.Size = 12;
                s52.Font.Bold = true;

                XmlStyle s53 = new XmlStyle();
                s53.Alignment.Vertical = VerticalAlignment.Bottom;
                s53.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s53.Border.Sides = BorderSides.Top;
                s53.Border.LineStyle = Borderline.Double;
                s53.Border.Weight = 3;
                s53.Font.Name = "宋体";
                s53.Font.Size = 22;
                s53.Font.Bold = true;

                XmlStyle s54 = new XmlStyle();
                s54.Alignment.Vertical = VerticalAlignment.Bottom;
                s54.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s54.Font.Name = "宋体";
                s54.Font.Size = 16;

                XmlStyle s61 = new XmlStyle();
                s61.Alignment.Vertical = VerticalAlignment.Bottom;
                s61.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;

                sheet[0, 0].Value = comTitle;
                sheet[0, 0].Style = s51;
                new Range(sheet[0, 0], sheet[7, 0]).Merge();

                sheet[0, 1].Value = "GUANGZHOU  YINGHONG  LOGISTICS  CO.,LTD";
                sheet[0, 1].Style = s52;
                new Range(sheet[0, 1], sheet[7, 1]).Merge();

                sheet[0, 2].Value = "报关委托书";
                sheet[0, 2].Style = s53;
                new Range(sheet[0, 2], sheet[7, 2]).Merge();

                sheet[0, 3].Value = "TO：" + bgAgent;
                sheet[0, 3].Style = s46;
                new Range(sheet[0, 3], sheet[7, 3]).Merge();

                sheet[0, 4].Value = "FM：盈泓/" + opName + "   " + opTel;
                sheet[0, 4].Style = s46;
                new Range(sheet[0, 4], sheet[7, 4]).Merge();

                sheet[0, 5].Value = "";
                sheet[0, 5].Style = s54;
                new Range(sheet[0, 5], sheet[7, 5]).Merge();

                sheet[0, 6].Value = "我司单号：" + orderCode;
                sheet[0, 6].Style = s46;
                new Range(sheet[0, 6], sheet[7, 6]).Merge();

                sheet[0, 7].Value = "订 舱 号：" + cCode;
                sheet[0, 7].Style = s46;
                new Range(sheet[0, 7], sheet[7, 7]).Merge();

                sheet[0, 8].Value = "起运码头：" + startPort;
                sheet[0, 8].Style = s46;
                new Range(sheet[0, 8], sheet[7, 8]).Merge();

                sheet[0, 9].Value = "截关时间：" + jgTime;
                sheet[0, 9].Style = s46;
                new Range(sheet[0, 9], sheet[7, 9]).Merge();

                sheet[0, 10].Value = "交放行条地址：" + fxtAddress;
                sheet[0, 10].Style = s46;
                new Range(sheet[0, 10], sheet[7, 10]).Merge();

                sheet[0, 11].Value = "报关备注：" + (string.IsNullOrEmpty(memo) ? "外拖/国际中转" : memo);//transferType + "/" + zgxs;
                sheet[0, 11].Style = s46;
                new Range(sheet[0, 11], sheet[7, 11]).Merge();

                sheet[0, 12].Value = "";
                sheet[0, 12].Style = s46;
                new Range(sheet[0, 12], sheet[7, 12]).Merge();

                sheet[0, 13].Value = "货物清单：";
                sheet[0, 13].Style = s46;
                new Range(sheet[0, 13], sheet[7, 13]).Merge();

                sheet[0, 14].Value = "柜号/柜型";
                sheet[0, 14].Style = s25;
                new Range(sheet[0, 14], sheet[2, 14]).Merge();

                sheet[3, 14].Value = "封号";
                sheet[3, 14].Style = s25;
                new Range(sheet[3, 14], sheet[4, 14]).Merge();

                sheet[5, 14].Value = "数量";
                sheet[5, 14].Style = s25;

                sheet[6, 14].Value = "毛重";
                sheet[6, 14].Style = s25;

                sheet[7, 14].Value = "货名";
                sheet[7, 14].Style = s25;
                var index = 15;
                for (var i = 0; i < cargos.Count; i++)
                {
                    sheet[0, index].Value = cargos[i][0];
                    sheet[0, index].Style = s25;
                    new Range(sheet[0, index], sheet[2, index]).Merge();

                    sheet[3, index].Value = cargos[i][1];
                    sheet[3, index].Style = s25;
                    new Range(sheet[3, index], sheet[4, index]).Merge();

                    sheet[5, index].Value = cargos[i][2];
                    sheet[5, index].Style = s25;

                    sheet[6, index].Value = cargos[i][3];
                    sheet[6, index].Style = s25;

                    sheet[7, index].Value = cargos[i][4];
                    sheet[7, index].Style = s25;
                    index++;
                }

                sheet[0, index].Value = "费用确认：" + fee + "元";
                sheet[0, index].Style = s47;
                new Range(sheet[0, index], sheet[7, index]).Merge();

                index++;
                sheet[0, index].Value = "";
                sheet[0, index].Style = s47;
                new Range(sheet[0, index], sheet[7, index]).Merge();

                index++;
                sheet[0, index].Value = "请尽快安排报关!放行后请通知我司！";
                sheet[0, index].Style = s46;
                new Range(sheet[0, index], sheet[7, index]).Merge();

                book.Export(contextResponse.OutputStream);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取账单
        /// </summary>
        /// <param name="contextResponse"></param>
        /// <param name="sDate"></param>
        /// <param name="eDate"></param>
        /// <param name="feeType"></param>
        /// <param name="target"></param>
        /// <param name="export"></param>
        void getBill(HttpResponse contextResponse, DateTime sDate, DateTime eDate, EnumCommon.FeeType feeType, int target, bool export)
        {

            BaseDataSvc.BaseDataClient BaseDataProxy = new BaseDataClient();
            string rtn = string.Empty;
            try
            {
                BaseDataProxy.Open();
                var dt = BaseDataProxy.GetOrderBill(target, sDate, eDate, (int)feeType);

                if (dt != null && dt.Rows.Count > 0)
                {
                    IEnumerable<TB_Options> os = BaseDataProxy.FindOptions(EnumCommon.OptionType.费目类型.ToString(), null, null);
                    foreach (DataRow dr in dt.Rows)
                    {

                        int orderId = Convert.ToInt32(dr["orderId"]);
                        TB_Case[] cases = BaseDataProxy.GetCases(orderId);
                        if (cases != null)
                        {
                            var models = from m in cases select m.model;
                            var codes = from m in cases select m.code;
                            var titles = from m in cases select m.title;
                            dr.SetField<string>("箱型", dr.Field<string>("箱型") + string.Join("\n", models.ToArray<string>()));
                            dr.SetField<string>("箱号", dr.Field<string>("箱号") + string.Join("\n", codes.ToArray<string>()));
                            dr.SetField<string>("封号", dr.Field<string>("封号") + string.Join("\n", titles.ToArray<string>()));
                        }

                        IEnumerable<TB_Fee> fees = BaseDataProxy.GetFees(orderId);

                        decimal total = 0;
                        if (fees != null)
                        {
                            fees = fees.Where(p => p.feeType == (int)feeType && p.isVerify);

                            if (EnumCommon.FeeType.应付 == feeType)
                                fees = fees.Where(p => p.target == target);

                            var q = from f in fees.Where(p => p.feeType == (int)feeType && p.isVerify)
                                    join o in os on f.item equals o.id
                                    select (o.name + "：" + f.amount.ToString());

                            dr.SetField<string>("费用", dr.Field<string>("费用") + string.Join("\n", q.ToArray<string>()));
                            total = fees.Sum(s => s.amount);
                            dr.SetField<decimal>("总额", total);
                        }
                    }
                    dt.AcceptChanges();
                    if (!export)
                    {
                        contextResponse.ContentType = "application/json";
                        contextResponse.Cache.SetNoStore();
                        //Hashtable ht = new Hashtable();
                        //ht.Add("total", dt.Rows.Count);
                        //ht.Add("rows", dt);
                        //string jsonStr = new JavaScriptSerializer().Serialize(ht);
                        string jsonStr = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":" + WebCommon.DataTableToJson(dt) + "}";
                        contextResponse.Write(jsonStr);
                    }
                    else
                    {
                        ExcelXmlWorkbook book = new ExcelXmlWorkbook();
                        contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 
                        contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", "账单.xls")); //定义输出文件和文件名
                        //contextResponse.AppendHeader("Content-Length", content.Length.ToString());
                        contextResponse.ContentEncoding = Encoding.Default;
                        contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 

                        book.Properties.Author = "盈泓";
                        Worksheet sheet = book[0];
                        sheet.Name = "账单";

                        // and this too...
                        sheet.PrintOptions.Orientation = PageOrientation.Landscape;
                        sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);

                        AlignmentOptions al = new AlignmentOptions();
                        al.Vertical = VerticalAlignment.Center;



                        AlignmentOptions al1 = new AlignmentOptions();
                        al1.Vertical = VerticalAlignment.Center;
                        al1.Horizontal = HorizontalAlignment.Center;

                        XmlStyle style = new XmlStyle();
                        style.Font.Size = 12;
                        style.Font.Name = "宋体";
                        style.Alignment = al;

                        XmlStyle style21 = new XmlStyle();
                        style21.Font.Size = 26;
                        style21.Font.Name = "隶书";
                        style21.Alignment = al1;


                        XmlStyle style22 = new XmlStyle();
                        style22.Font.Size = 14;
                        style22.Font.Name = "华文新魏";
                        style22.Alignment = al1;
                        style22.Font.Bold = true;

                        XmlStyle style23 = new XmlStyle();
                        style23.Font.Size = 9;
                        style23.Font.Name = "宋体";
                        style23.Alignment = al1;

                        XmlStyle style24 = new XmlStyle();
                        style24.Font.Size = 15;
                        style24.Font.Name = "宋体";
                        style24.Alignment = al1;
                        style22.Font.Bold = true;

                        XmlStyle style28 = new XmlStyle();
                        style28.Font.Size = 13;
                        style28.Font.Name = "宋体";
                        style28.Alignment = al1;
                        style28.Font.Bold = true;
                        style28.Border.Weight = 1;
                        style28.Border.LineStyle = Borderline.Continuous;
                        style28.Border.Sides = BorderSides.All;

                        XmlStyle style29 = new XmlStyle();
                        style29.Border.Weight = 1;
                        style29.Border.LineStyle = Borderline.Continuous;
                        style29.Border.Sides = BorderSides.All;

                        XmlStyle style30 = new XmlStyle();
                        style30.Border.Weight = 1;
                        style30.Border.LineStyle = Borderline.Continuous;
                        style30.Border.Sides = BorderSides.All;
                        style30.DisplayFormat = DisplayFormatType.ShortDate;

                        XmlStyle style31 = new XmlStyle();
                        style31.Alignment.Vertical = VerticalAlignment.Center;
                        style31.Alignment.WrapText = true;
                        style31.Border.Weight = 1;
                        style31.Border.LineStyle = Borderline.Continuous;
                        style31.Border.Sides = BorderSides.All;

                        XmlStyle style35 = new XmlStyle();
                        style35.Alignment = al1;
                        style35.Font.Size = 12;
                        style35.Font.Name = "宋体";
                        style35.Font.Bold = true;

                        XmlStyle style36 = new XmlStyle();
                        style36.Alignment.Vertical = VerticalAlignment.Center;
                        style36.Alignment.Horizontal = HorizontalAlignment.Center;
                        style36.Alignment.WrapText = true;
                        style36.Font.Size = 12;
                        style36.Font.Name = "宋体";
                        style36.Font.Bold = true;

                        XmlStyle style37 = new XmlStyle();
                        style37.Alignment = al1;
                        style37.Font.Size = 12;
                        style37.Font.Name = "宋体";
                        style37.Font.Bold = true;
                        style37.DisplayFormat = DisplayFormatType.ShortDate;

                        XmlStyle style39 = new XmlStyle();
                        style39.Alignment = al1;
                        style39.Font.Size = 30;
                        style39.Font.Name = "宋体";
                        style39.Font.Bold = true;

                        XmlStyle style40 = new XmlStyle();
                        style40.Alignment = al1;
                        style40.Font.Size = 15;
                        style40.Font.Name = "黑体";
                        style40.Font.Bold = true;

                        XmlStyle style42 = new XmlStyle();
                        style42.Alignment.Horizontal = HorizontalAlignment.Left;
                        style42.Alignment.Vertical = VerticalAlignment.Center;
                        style42.Font.Size = 12;
                        style42.Font.Name = "宋体";
                        style42.Font.Bold = true;

                        sheet[0, 0].Value = "广州市科洋货运代理有限公司";
                        sheet[0, 0].Style = style39;
                        sheet[0, 0].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, 0], sheet[8, 0]).Merge();

                        sheet[0, 1].Value = "Guangzhou Keyang Transportation Agency Co.Ltd.";
                        sheet[0, 1].Style = style40;
                        sheet[0, 1].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, 1], sheet[8, 1]).Merge();

                        sheet[0, 2].Value = "账单明细";
                        sheet[0, 2].Style = style24;
                        sheet[0, 2].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, 2], sheet[8, 2]).Merge();

                        string targetName = string.Empty;
                        if (feeType == EnumCommon.FeeType.应收)
                        {
                            TB_Clients client = BaseDataProxy.GetClient(target);
                            targetName = client.fullName;
                        }
                        else
                        {
                            TB_Options o = BaseDataProxy.GetOption(target);
                            targetName = o.name;
                        }
                        sheet[0, 3].Value = "致：" + targetName;
                        sheet[0, 3].Style = style42;
                        sheet[0, 3].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, 3], sheet[8, 3]).Merge();

                        int sumIndex = 0;
                        //组织列头
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (dt.Columns[i].ColumnName == "总额")
                                sumIndex = i;
                            sheet[i, 4].Value = dt.Columns[i].Caption;
                            sheet[i, 4].Style = style28;
                            sheet[i, 4].DisplayFormat = DisplayFormatType.Text;
                        }
                        int currentRow = 5;
                        decimal totalAmount = 0;

                        //加载表数据
                        for (int r = 0; r < dt.Rows.Count; r++)
                        {
                            int row = currentRow + r;
                            for (int c = 0; c < dt.Rows.Count; c++)
                            {
                                sheet[c, row].Value = dt.Rows[r][c];
                                if (dt.Columns[c].ColumnName == "装货日期")
                                {
                                    sheet[c, row].Style = style30;
                                    sheet[c, row].DisplayFormat = DisplayFormatType.Custom;
                                    sheet[c, row].CustomFormatString = "yyyy-MM-dd HH:mm";
                                }
                                else if (dt.Columns[c].ColumnName == "费用")
                                {
                                    sheet[c, row].Style = style31;
                                    sheet[c, row].DisplayFormat = DisplayFormatType.Text;
                                    if (!WebCommon.IsCellNull(sheet[c, row].Value))
                                        totalAmount += Convert.ToDecimal(sheet[c, row].Value);
                                }
                                else if (dt.Columns[c].ColumnName == "总额")
                                {

                                    sheet[c, row].Style = style29;
                                    sheet[c, row].DisplayFormat = DisplayFormatType.Custom;
                                    sheet[c, row].CustomFormatString = "N2";

                                }
                                else
                                {
                                    sheet[c, row].Style = style29;
                                    sheet[c, row].DisplayFormat = DisplayFormatType.Text;
                                }
                            }
                        }
                        currentRow += dt.Rows.Count;
                        sheet[0, currentRow].Value = "总计：";
                        sheet[0, currentRow].Style = style28;
                        sheet[0, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, currentRow], sheet[6, currentRow]).Merge();

                        currentRow += 1;
                        sheet[dt.Columns.Count - 2, currentRow].Value = FormulaHelper.Formula("sum", new Range(sheet[sumIndex, 5], sheet[sumIndex, 4 + dt.Rows.Count]));
                        sheet[dt.Columns.Count - 2, currentRow].Style = style28;
                        sheet[dt.Columns.Count - 2, currentRow].DisplayFormat = DisplayFormatType.Custom;
                        sheet[dt.Columns.Count - 2, currentRow].CustomFormatString = "N2";

                        currentRow += 1;
                        sheet[0, currentRow].Value = "开户行：中国工商银行-黄埔大道西支行";
                        sheet[0, currentRow].Style = style35;
                        sheet[0, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, currentRow], sheet[3, currentRow]).Merge();

                        sheet[0, currentRow].Value = "请贵公司在收到账单后3个工作日内确认费用，否则当作默认处理。";
                        sheet[0, currentRow].Style = style36;
                        sheet[0, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, currentRow], sheet[4, currentRow + 4]).Merge();

                        currentRow += 1;
                        sheet[0, currentRow].Value = "卡号：6222 0236 0204 0740 607";
                        sheet[0, currentRow].Style = style35;
                        sheet[0, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, currentRow], sheet[3, currentRow]).Merge();

                        currentRow += 1;
                        sheet[0, currentRow].Value = "用户名：黄绍官";
                        sheet[0, currentRow].Style = style35;
                        sheet[0, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, currentRow], sheet[3, currentRow]).Merge();

                        currentRow += 1;
                        sheet[0, currentRow].Value = "（如需要其它帐号，请看第二页的银行帐号）";
                        sheet[0, currentRow].Style = style35;
                        sheet[0, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, currentRow], sheet[3, currentRow]).Merge();

                        currentRow += 1;
                        sheet[0, currentRow].Value = "如费用无误请尽快付款，谢谢！付款时，麻烦给予水单，方便销账！顺祝商祺！";
                        sheet[0, currentRow].Style = style36;
                        sheet[0, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[0, currentRow], sheet[3, currentRow + 5]).Merge();

                        sheet[4, currentRow].Value = "制单人：黄先生/marco";
                        sheet[4, currentRow].Style = style35;
                        sheet[4, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[4, currentRow], sheet[8, currentRow]).Merge();

                        currentRow += 1;
                        sheet[4, currentRow].Value = "电话：020-28945185";
                        sheet[4, currentRow].Style = style35;
                        sheet[4, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[4, currentRow], sheet[8, currentRow]).Merge();

                        currentRow += 1;
                        sheet[4, currentRow].Value = "传真：020-89718757";
                        sheet[4, currentRow].Style = style35;
                        sheet[4, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[4, currentRow], sheet[8, currentRow]).Merge();

                        currentRow += 2;
                        sheet[4, currentRow].Value = "广州市科洋货运代理财务部";
                        sheet[4, currentRow].Style = style35;
                        sheet[4, currentRow].DisplayFormat = DisplayFormatType.Text;
                        new Range(sheet[4, currentRow], sheet[8, currentRow]).Merge();

                        currentRow += 1;
                        sheet[4, currentRow].Value = DateTime.Now;
                        sheet[4, currentRow].Style = style35;
                        sheet[4, currentRow].DisplayFormat = DisplayFormatType.Custom;
                        sheet[4, currentRow].CustomFormatString = "yyyy-MM-dd";
                        new Range(sheet[4, currentRow], sheet[8, currentRow]).Merge();

                        Worksheet sheet1 = book[1];
                        sheet1.Name = "账号";
                        sheet[0, 0].Value = "广州市科洋货运代理有限公司";
                        sheet[0, 0].Style = style21;
                        sheet[0, 0].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 1].Value = "Guangzhou Keyang Transportation Agency Co.,Ltd.";
                        sheet[0, 1].Style = style22;
                        sheet[0, 1].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 2].Value = "电话Tel: 020-28945185，传真Fax:020-89718757";
                        sheet[0, 2].Style = style23;
                        sheet[0, 2].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 3].Value = "地址Add：广州市黄埔区黄埔东路5号东盛国际1616室";
                        sheet[0, 3].Style = style23;
                        sheet[0, 3].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 4].Value = "开户行:中国工商银行-黄埔大道西支行";
                        sheet[0, 4].Style = style24;
                        sheet[0, 4].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 5].Value = "卡号:6222 0236 0204 0740 607";
                        sheet[0, 5].Style = style24;
                        sheet[0, 5].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 6].Value = "用户名:黄绍官";
                        sheet[0, 6].Style = style24;
                        sheet[0, 6].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 7].Value = "开户行:中国银行-广州港湾路支行";
                        sheet[0, 7].Style = style24;
                        sheet[0, 7].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 8].Value = "卡号:6216 6119 0000 1711 702";
                        sheet[0, 8].Style = style24;
                        sheet[0, 8].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 9].Value = "用户名:黄绍官";
                        sheet[0, 9].Style = style24;
                        sheet[0, 9].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 10].Value = "开户行:中国农业银行-黄埔支行";
                        sheet[0, 10].Style = style24;
                        sheet[0, 10].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 11].Value = "卡号:6228 4800 8410 1257 115";
                        sheet[0, 11].Style = style24;
                        sheet[0, 11].DisplayFormat = DisplayFormatType.Text;

                        sheet[0, 12].Value = "用户名:黄绍官";
                        sheet[0, 12].Style = style24;
                        sheet[0, 12].DisplayFormat = DisplayFormatType.Text;

                        book.Export(contextResponse.OutputStream);
                    }
                }
                BaseDataProxy.Close();
            }
            catch (Exception ex) { throw ex; }

        }

        void PrintBillPre(bool export, HttpContext context, HttpResponse contextResponse, DateTime sDate, DateTime eDate, EnumCommon.FeeType feeType, int target, int? skAccount, string[] ckIdAry)
        {
            var comTitle = "广州市盈泓货运代理有限公司";

            var targetName = string.Empty;
            var contactMan = string.Empty;
            var contactTel = string.Empty;
            var fax = string.Empty;

            try
            {
                TB_User user = context.Session["User"] as TB_User;

                BaseDataSvc.BaseDataClient BaseDataProxy = new BaseDataClient();
                BaseDataProxy.Open();
                if (feeType == EnumCommon.FeeType.应收)
                {
                    var client = BaseDataProxy.GetClient(target);
                    targetName = client.fullName;
                    contactMan = client.contactMan;
                    contactTel = client.tel;
                    fax = client.fax;

                }
                else
                {
                    var option = BaseDataProxy.GetOption(target);
                    targetName = option.name;
                }

                var opName = user.userName;
                var opTel = "020-66206216";
                var opFax = "020-82595753";
                DataTable dt = BaseDataProxy.GetOrderBill(target, sDate, eDate, (int)feeType);
                if (dt != null && dt.Rows.Count > 0)
                {
                    IEnumerable<TB_Options> os = BaseDataProxy.FindOptions(EnumCommon.OptionType.费目类型.ToString(), null, null);
                    dt.Columns.Add("ckid");
                    int ckid = 0;
                    bool skipRow = export & ckIdAry != null;
                    foreach (DataRow dr in dt.Rows)
                    {
                        ckid++;
                        if (skipRow)
                            if (!ckIdAry.Any(p => p == ckid.ToString()))
                            {
                                dr.Delete();
                                continue;
                            }
                        dr["ckid"] = ckid;

                        int orderId = Convert.ToInt32(dr["orderId"]);
                        TB_Case[] cases = BaseDataProxy.GetCases(orderId);
                        if (cases != null)
                        {
                            var models = from m in cases select m.model;
                            var codes = from m in cases select m.code;
                            var titles = from m in cases select m.title;
                            dr.SetField<string>("箱型", dr.Field<string>("箱型") + string.Join("\n", models.ToArray<string>()));
                            dr.SetField<string>("箱号", dr.Field<string>("箱号") + string.Join("\n", codes.ToArray<string>()));
                            dr.SetField<string>("封号", dr.Field<string>("封号") + string.Join("\n", titles.ToArray<string>()));
                        }

                        IEnumerable<TB_Fee> fees = BaseDataProxy.GetFees(orderId);

                        decimal total = 0;
                        if (fees != null)
                        {
                            fees = fees.Where(p => p.feeType == (int)feeType && p.isVerify);

                            if (EnumCommon.FeeType.应付 == feeType)
                                fees = fees.Where(p => p.target == target);

                            var q = from f in fees.Where(p => p.feeType == (int)feeType && p.isVerify)
                                    join o in os on f.item equals o.id
                                    select (o.name + "：" + f.amount.ToString());

                            dr.SetField<string>("费用", dr.Field<string>("费用") + string.Join("\n", q.ToArray<string>()));
                            total = fees.Sum(s => s.amount);
                            dr.SetField<decimal>("总额", total);
                        }
                    }
                    dt.AcceptChanges();
                    PrintBill(export, contextResponse, BaseDataProxy, dt, skAccount, comTitle, sDate.ToString("yyyy-MM-dd"), eDate.ToString("yyyy-MM-dd"), targetName, contactMan, contactTel, fax, opName, opTel, opFax);
                }
                BaseDataProxy.Close();
            }
            catch { }
        }

        void PrintBill(bool export, HttpResponse contextResponse, BaseDataSvc.BaseDataClient BaseDataProxy, DataTable dt, int? skAccount, string comTitle, string sDate, string eDate, string targetName, string contactMan, string contactTel, string fax, string opName, string opTel, string opFax)
        {

            //string rtn = string.Empty;
            try
            {
                if (!export)
                {
                    contextResponse.ContentType = "application/json";
                    contextResponse.Cache.SetNoStore();
                    //Hashtable ht = new Hashtable();
                    //ht.Add("total", dt.Rows.Count);
                    //ht.Add("rows", dt);
                    //string jsonStr = new JavaScriptSerializer().Serialize(ht);
                    string jsonStr = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":" + WebCommon.DataTableToJson(dt) + "}";
                    contextResponse.Write(jsonStr);
                }
                else
                {

                    ExcelXmlWorkbook book = new ExcelXmlWorkbook();
                    contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 
                    contextResponse.AppendHeader("Content-Disposition", string.Format("attachment;filename=账单{0}至{1}.xls", sDate, eDate)); //定义输出文件和文件名
                    //contextResponse.AppendHeader("Content-Length", content.Length.ToString());
                    contextResponse.ContentEncoding = Encoding.Default;
                    contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 

                    book.Properties.Author = "盈泓";
                    Worksheet sheet = book[0];
                    sheet.Name = "账单明细";

                    // and this too...
                    sheet.PrintOptions.Orientation = PageOrientation.Portrait;
                    sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);


                    XmlStyle ms = new XmlStyle();
                    ms.Font.Size = 11;
                    ms.Font.Name = "Swiss";
                    ms.Alignment.Vertical = VerticalAlignment.Center;
                    ms.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    ms.Alignment.WrapText = true;
                    ms.Font.Bold = true;
                    ms.Border.LineStyle = Borderline.Continuous;
                    ms.Border.Weight = 2;
                    ms.Border.Sides = BorderSides.All;

                    XmlStyle s21 = new XmlStyle();
                    s21.Font.Size = 12;
                    s21.Font.Name = "宋体";
                    s21.Alignment.Vertical = VerticalAlignment.Center;
                    s21.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s21.Font.Bold = true;

                    XmlStyle s22 = new XmlStyle();
                    s22.Font.Size = 11;
                    s22.Font.Name = "Swiss";
                    s22.Alignment.Vertical = VerticalAlignment.Center;
                    s22.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s22.Alignment.WrapText = true;
                    s22.Font.Bold = true;
                    s22.Border.LineStyle = Borderline.Continuous;
                    s22.Border.Weight = 2;
                    s22.Border.Sides = BorderSides.All;

                    XmlStyle s23 = new XmlStyle();
                    s23.Font.Size = 11;
                    s23.Font.Name = "Swiss";
                    s23.Alignment.Vertical = VerticalAlignment.Center;
                    s23.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s23.Alignment.WrapText = true;
                    s23.Font.Bold = true;
                    s23.Border.LineStyle = Borderline.Continuous;
                    s23.Border.Weight = 2;
                    s23.Border.Sides = BorderSides.Left;
                    s23.Border.Sides = BorderSides.Right;
                    s23.Border.Sides = BorderSides.Top;

                    XmlStyle s24 = new XmlStyle();
                    s24.Font.Size = 11;
                    s24.Font.Name = "Swiss";
                    s24.Alignment.Vertical = VerticalAlignment.Center;
                    s24.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s24.Alignment.WrapText = true;
                    s24.Font.Bold = true;
                    s24.Border.LineStyle = Borderline.Continuous;
                    s24.Border.Weight = 2;
                    s24.Border.Sides = BorderSides.Left;
                    s24.Border.Sides = BorderSides.Right;
                    s24.Border.Sides = BorderSides.Bottom;

                    XmlStyle s25 = new XmlStyle();
                    s25.Font.Size = 11;
                    s25.Font.Name = "Swiss";
                    s24.Alignment.Vertical = VerticalAlignment.Center;
                    s24.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s24.Alignment.WrapText = true;
                    s24.Font.Bold = true;
                    s24.Border.LineStyle = Borderline.Continuous;
                    s24.Border.Weight = 2;
                    s24.Border.Sides = BorderSides.Left;
                    s24.Border.Sides = BorderSides.Right;

                    XmlStyle s26 = new XmlStyle();
                    s26.Font.Size = 11;
                    s26.Font.Name = "Swiss";
                    s26.Alignment.Vertical = VerticalAlignment.Center;
                    s26.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s26.Alignment.WrapText = true;
                    s26.Font.Bold = true;

                    XmlStyle s27 = new XmlStyle();
                    s27.Font.Size = 11;
                    s27.Font.Name = "宋体";
                    s27.Alignment.Vertical = VerticalAlignment.Center;
                    s27.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s27.Alignment.WrapText = true;
                    s27.Font.Bold = true;
                    s27.Border.LineStyle = Borderline.Continuous;
                    s27.Border.Weight = 2;
                    s27.Border.Sides = BorderSides.Left;
                    s27.Border.Sides = BorderSides.Right;
                    s27.Border.Sides = BorderSides.Top;

                    XmlStyle s28 = new XmlStyle();
                    s28.Font.Size = 11;
                    s28.Font.Name = "宋体";
                    s28.Alignment.Vertical = VerticalAlignment.Center;
                    s28.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s28.Alignment.WrapText = true;
                    s28.Font.Bold = true;
                    s28.Border.LineStyle = Borderline.Continuous;
                    s28.Border.Weight = 2;
                    s28.Border.Sides = BorderSides.Left;
                    s28.Border.Sides = BorderSides.Right;
                    s28.Border.Sides = BorderSides.Bottom;

                    XmlStyle s29 = new XmlStyle();
                    s29.Font.Size = 11;
                    s29.Font.Name = "宋体";
                    s29.Alignment.Vertical = VerticalAlignment.Center;
                    s29.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s29.Alignment.WrapText = true;
                    s29.Font.Bold = true;
                    s29.Border.LineStyle = Borderline.Continuous;
                    s29.Border.Weight = 2;
                    s29.Border.Sides = BorderSides.Left;
                    s29.Border.Sides = BorderSides.Right;

                    XmlStyle s44 = new XmlStyle();
                    s44.Font.Size = 12;
                    s44.Font.Name = "宋体";
                    s44.Alignment.Vertical = VerticalAlignment.Center;
                    s44.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                    s44.Alignment.WrapText = true;
                    s44.Font.Bold = true;

                    XmlStyle s46 = new XmlStyle();
                    s46.Font.Size = 22;
                    s46.Font.Name = "华文新魏";
                    s46.Alignment.Vertical = VerticalAlignment.Center;
                    s46.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                    s46.Alignment.WrapText = true;
                    s46.Font.Bold = true;

                    XmlStyle s47 = new XmlStyle();
                    s47.Font.Size = 12;
                    s47.Font.Name = "宋体";
                    s47.Alignment.Vertical = VerticalAlignment.Center;
                    s47.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right;
                    s47.Alignment.WrapText = true;
                    s47.Font.Bold = true;

                    XmlStyle s48 = new XmlStyle();
                    s48.Font.Size = 24;
                    s48.Font.Name = "宋体";
                    s48.Alignment.Vertical = VerticalAlignment.Center;
                    s48.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s48.Font.Bold = true;

                    XmlStyle s49 = new XmlStyle();
                    s49.Font.Size = 18;
                    s49.Font.Name = "宋体";
                    s49.Alignment.Vertical = VerticalAlignment.Center;
                    s49.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s49.Font.Bold = true;

                    XmlStyle s50 = new XmlStyle();
                    s50.Font.Size = 14;
                    s50.Font.Name = "Times New Roman";
                    s50.Alignment.Vertical = VerticalAlignment.Center;
                    s50.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                    s50.Font.Bold = true;
                    s50.Border.LineStyle = Borderline.Double;
                    s50.Border.Sides = BorderSides.Bottom;
                    s50.Border.Weight = 3;

                    XmlStyle s57 = new XmlStyle();
                    s57.Font.Size = 12;
                    s57.Font.Name = "宋体";
                    s57.Alignment.Vertical = VerticalAlignment.Center;
                    s57.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                    s57.Font.Bold = true;

                    XmlStyle s58 = new XmlStyle();
                    s58.Font.Size = 12;
                    s58.Font.Name = "宋体";
                    s58.Alignment.WrapText = true;
                    s58.Alignment.Vertical = VerticalAlignment.Center;
                    s58.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                    s58.Font.Bold = true;

                    XmlStyle s59 = new XmlStyle();
                    s59.Font.Size = 12;
                    s59.Font.Name = "宋体";
                    s59.Alignment.Vertical = VerticalAlignment.Center;
                    s59.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                    s59.Font.Bold = true;
                    s59.Border.LineStyle = Borderline.Continuous;
                    s59.Border.Sides = BorderSides.Bottom;
                    s59.Border.Weight = 2;

                    int currentRow = 0;
                    sheet[0, currentRow].Value = comTitle;
                    sheet[0, currentRow].Style = s48;
                    new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[0, currentRow].Value = "GUANGZHOU YINGHONG LOGISTICS CO.,LTD";
                    sheet[0, currentRow].Style = s50;
                    new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[0, currentRow].Value = sDate + "至" + eDate + "帐单明细";
                    sheet[0, currentRow].Style = s49;
                    new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[0, currentRow].Value = "致：" + targetName + "  联系人:" + contactMan + " 电话：" + contactTel + "  传真：" + fax;
                    sheet[0, currentRow].Style = s59;
                    new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[0, currentRow].Value = "我司单号";
                    sheet[0, currentRow].Style = s22;
                    sheet[1, currentRow].Value = "提单号";
                    sheet[1, currentRow].Style = s22;
                    sheet[2, currentRow].Value = "箱号/箱型";
                    sheet[2, currentRow].Style = s22;
                    sheet[3, currentRow].Value = "明细";
                    sheet[3, currentRow].Style = s22;
                    sheet[4, currentRow].Value = "小计";
                    sheet[4, currentRow].Style = s22;
                    decimal totalAmount = 0;
                    if (dt != null)
                    {
                        if (dt.Rows.Count > 0)
                        {
                            //IEnumerable<TB_Case> cases = this.BaseDataProxy.GetCases(orderId);
                            int merginRows = 0;
                            foreach (DataRow dr in dt.Rows)
                            {
                                int orderId = dr.Field<int>("orderId");
                                TB_Order order = BaseDataProxy.GetOrder(orderId);
                                currentRow++;
                                string[] ary = dr.Field<string>("费用").Split('\n');

                                sheet[0, currentRow].Value = order.code;
                                sheet[0, currentRow].Style = s22;
                                sheet[1, currentRow].Value = dr.Field<string>("订舱号");
                                sheet[1, currentRow].Style = s22;
                                string[] xhAry = dr.Field<string>("箱号").Split('\n');
                                string[] xxAry = dr.Field<string>("箱型").Split('\n');
                                string xhxx = string.Empty;
                                if (xhAry.Length == xxAry.Length)
                                {
                                    for (int s = 0; s < xhAry.Length; s++)
                                    {
                                        var str = xhAry[s] + "/" + xxAry[s];
                                        if (s < xxAry.GetUpperBound(0))
                                            str += System.Environment.NewLine;
                                        xhxx += str;
                                    }
                                }
                                sheet[2, currentRow].Value = xhxx;
                                sheet[2, currentRow].Style = s22;
                                sheet[4, currentRow].Value = dr.Field<decimal>("总额");
                                sheet[4, currentRow].Style = s22;
                                totalAmount += dr.Field<decimal>("总额");
                                if (ary.Length > 1)
                                {
                                    var margin = currentRow + ary.Length - 1;
                                    new Range(sheet[0, currentRow], sheet[0, margin]).Merge();
                                    new Range(sheet[1, currentRow], sheet[1, margin]).Merge();
                                    new Range(sheet[2, currentRow], sheet[2, margin]).Merge();
                                    new Range(sheet[4, currentRow], sheet[4, margin]).Merge();
                                    merginRows += ary.Length - 1;
                                }

                                for (var i = 0; i < ary.Length; i++)
                                {
                                    if (i > 0)
                                        currentRow++;
                                    sheet[3, currentRow].Value = ary[i];
                                    sheet[3, currentRow].Style = ms;
                                }


                            }
                            currentRow++;
                            sheet[0, currentRow].Value = "合计：";
                            sheet[0, currentRow].Style = s22;
                            new Range(sheet[0, currentRow], sheet[3, currentRow]).Merge();
                            var dts = currentRow - dt.Rows.Count - merginRows;
                            var dte = currentRow - 1;
                            //var sum = FormulaHelper.Formula("sum", new Range(sheet[4, dts], sheet[4, dte]), cell => cell.Style == s22);
                            sheet[4, currentRow].Value = totalAmount;
                            sheet[4, currentRow].Style = s22;

                        }
                    }

                    currentRow++;
                    sheet[0, currentRow].Value = "";
                    sheet[0, currentRow].Style = s26;

                    currentRow++;
                    sheet[0, currentRow].Value = "备注：1、请收到此账单三天内签章回传至020-82595753，三天内未回传视同默认";
                    sheet[0, currentRow].Style = s58;
                    new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[0, currentRow].Value = "      2、如需开发票请注明抬头、币种、联系人、电话与邮寄地址";
                    sheet[0, currentRow].Style = s58;
                    new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[0, currentRow].Value = "****付款需付入我司以下指定帐户，并传水单";
                    sheet[0, currentRow].Style = s57;
                    new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    string account = null, bankName = null, bankAccount = null, bankUserName = null;
                    if (skAccount.HasValue)
                    {
                        TB_Options option = BaseDataProxy.GetOption(skAccount.Value);
                        if (option != null)
                            account = option.name;
                    }
                    if (!string.IsNullOrEmpty(account))
                    {
                        var arr = account.Split(new char[] { ',', '，' });
                        bankName = arr[0];
                        bankAccount = arr[1];
                        bankUserName = arr[2];
                    }

                    sheet[0, currentRow].Value = string.Format("开户行：{0}", string.IsNullOrEmpty(bankName) ? "工商银行——广州黄埔支行怡港支行" : bankName);
                    sheet[0, currentRow].Style = s44;
                    new Range(sheet[0, currentRow], sheet[2, currentRow]).Merge();

                    currentRow++;
                    sheet[0, currentRow].Value = string.Format("账  号：{0}", string.IsNullOrEmpty(bankAccount) ? "3602 0885 0920 0043 218" : bankAccount);
                    sheet[0, currentRow].Style = s44;
                    new Range(sheet[0, currentRow], sheet[2, currentRow]).Merge();

                    currentRow++;
                    sheet[0, currentRow].Value = string.Format("用户名：{0}", string.IsNullOrEmpty(bankUserName) ? "广州市盈泓货运代理有限公司" : bankUserName);
                    sheet[0, currentRow].Style = s44;
                    new Range(sheet[0, currentRow], sheet[2, currentRow]).Merge();

                    sheet[3, currentRow].Value = "";
                    sheet[3, currentRow].Style = s44;
                    new Range(sheet[3, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[3, currentRow].Value = "制单人：" + opName;
                    sheet[3, currentRow].Style = s47;
                    new Range(sheet[3, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[3, currentRow].Value = "电话：" + opTel + "                           传真：" + opFax;
                    sheet[3, currentRow].Style = s47;
                    new Range(sheet[3, currentRow], sheet[4, currentRow]).Merge();

                    currentRow++;
                    sheet[3, currentRow].Value = "传真：" + opFax;
                    sheet[3, currentRow].Style = s47;
                    new Range(sheet[3, currentRow], sheet[4, currentRow]).Merge();

                    sheet.Columns(0).Width = 87.75;
                    sheet.Columns(1).Width = 130;
                    sheet.Columns(2).Width = 130;
                    sheet.Columns(3).Width = 137.25;
                    sheet.Columns(4).Width = 80.25;

                    book.Export(contextResponse.OutputStream);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}
